import tushare as ts
import pandas as pd
# my_password='c3656db467576c72937e892ac93354ac408d105214a81e2cd7701a37'
# pro = ts.pro_api(my_password)
# data = pro.query('stock_basic', exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')
# data.to_csv("股票信息.csv")
import pymysql
db_config = {
        'host': 'localhost',  # 数据库地址
        'user': 'root',  # 数据库用户名
        'password': '123456',  # 数据库密码
        'database': 'stockxxx',  # 数据库名称
        'charset': 'utf8mb4',  # 字符集
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 连接到数据库
connection = pymysql.connect(**db_config)
df = pd.read_csv('股票信息.csv',index_col=0)
df=df[0:5360]

try:
    with connection.cursor() as cursor:
        # 逐行处理数据
        for index, row in df.iterrows():
            # 插入数据的 SQL 语句
            sql = """
            INSERT INTO stocks_stockbasic (ts_code, symbol, name,area,industry,list_date)
            VALUES (%s, %s, %s,%s, %s, %s)
            """
            # 执行 SQL
            cursor.execute(sql, (row['ts_code'],row['symbol'] ,row['name'],row['area'],row['industry'] ,row['list_date'],))

    # 提交事务
    connection.commit()
    print("数据插入成功！")

finally:
    # 关闭数据库连接
    connection.close()